﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace 影楼婚纱摄影系统
{
    public partial class 业绩查询 : Form
    {
        public 业绩查询()
        {
            InitializeComponent();
        }


        private void 业绩查询_Load(object sender, EventArgs e)
        {
            //使用时间格式自定义格式
            this.dateTimePicker1.CustomFormat = "yyyy-MM";
            this.dateTimePicker1.Format = DateTimePickerFormat.Custom;
            this.dateTimePicker1.ShowUpDown = false;
        }


        private void button1_Click(object sender, EventArgs e)
        {
            if (textBox1.Text != "")
            {
                //数据库连接串
                string str = System.Configuration.ConfigurationManager.ConnectionStrings["marry"].ConnectionString;
                //创建SqlConnection的实例
                SqlConnection conn = null;
                try
                {
                    conn = new SqlConnection(str);
                    //打开数据库
                    conn.Open();
                    string sql = "select Year(ordertime) as 年, Month(ordertime) as 月 , SUM(price) as 订单总额, count(orderid) as 完成订单数, SUM(price)*(select percentage from staff where sId={0}) as 业务提成 from [order] where staffId like '%{0}%' and Year(ordertime)={1} and Month(ordertime)={2} group by Year(ordertime), Month(ordertime)";
                    //获取时间信息
                    string time = dateTimePicker1.Value.ToString();
                    //填充占位符
                    sql = string.Format(sql, textBox1.Text, dateTimePicker1.Value.ToString("yyyy"), dateTimePicker1.Value.ToString("MM"));
                    //创建SqlDataAdapter类的对象
                    SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
                    //创建DataSet类的对象
                    DataSet ds = new DataSet();
                    //使用SqlDataAdapter对象sda将查新结果填充到DataSet对象ds中
                    sda.Fill(ds);
                    //设置表格控件的DataSource属性
                    dataGridView1.DataSource = ds.Tables[0];
                }
                catch (Exception ex)
                {
                    MessageBox.Show("出现错误！" + ex.Message);
                }
                finally
                {
                    if (conn != null)
                    {
                        //关闭数据库连接
                        conn.Close();
                    }
                }
            }

            else
            {
                //数据库连接串
                string str = System.Configuration.ConfigurationManager.ConnectionStrings["marry"].ConnectionString;
                //创建SqlConnection的实例
                SqlConnection conn = null;
                try
                {
                    conn = new SqlConnection(str);
                    //打开数据库
                    conn.Open();
                    string sql = "select '1' as 员工ID,(select basesalary from staff where sId=1) as 基础工资,(select SUM(price)*(select percentage from staff where sId=1) from [order] where staffId like '%1%' and Year(ordertime)={0} and Month(ordertime)={1}) as 业绩 union select '2',(select basesalary from staff where sId=2) as 基础工资,(select SUM(price)*(select percentage from staff where sId=2) from [order] where staffId like '%2%' and Year(ordertime)={0} and Month(ordertime)= {1})  union  select '3',(select basesalary from staff where sId=3) as 基础工资,(select SUM(price)*(select percentage from staff where sId=3) from [order] where staffId like '%3%' and Year(ordertime)={0} and Month(ordertime)= {1})  union select '4',(select basesalary from staff where sId=4) as 基础工资,(select SUM(price)*(select percentage from staff where sId=4) from [order] where staffId like '%4%' and Year(ordertime)={0} and Month(ordertime)= {1})  union select '5',(select basesalary from staff where sId=5) as 基础工资,(select SUM(price)*(select percentage from staff where sId=5) from [order] where staffId like '%5%' and Year(ordertime)={0} and Month(ordertime)= {1}) union select '6',(select basesalary from staff where sId=6) as 基础工资,(select SUM(price)*(select percentage from staff where sId=6) from [order] where staffId like '%6%' and Year(ordertime)={0} and Month(ordertime)= {1}) union select '7',(select basesalary from staff where sId=7) as 基础工资,(select SUM(price)*(select percentage from staff where sId=7) from [order] where staffId like '%7%' and Year(ordertime)={0} and Month(ordertime)= {1}) union select '8',(select basesalary from staff where sId=8) as 基础工资,(select SUM(price)*(select percentage from staff where sId=8) from [order] where staffId like '%8%' and Year(ordertime)={0} and Month(ordertime)= {1})";
                    //获取时间信息

                    //填充占位符
                    sql = string.Format(sql, dateTimePicker1.Value.ToString("yyyy"), dateTimePicker1.Value.ToString("MM"));
                    //创建SqlDataAdapter类的对象
                    SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
                    //创建DataSet类的对象
                    DataSet ds = new DataSet();
                    //使用SqlDataAdapter对象sda将查新结果填充到DataSet对象ds中
                    sda.Fill(ds);
                    //设置表格控件的DataSource属性
                    dataGridView1.DataSource = ds.Tables[0];
                }
                catch (Exception ex)
                {
                    MessageBox.Show("出现错误！" + ex.Message);
                }
                finally
                {
                    if (conn != null)
                    {
                        //关闭数据库连接
                        conn.Close();
                    }
                }
            }
        }


    }
}
